﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using Microsoft.Practices.EnterpriseLibrary.Common;
using Microsoft.Practices.EnterpriseLibrary.Data;
using System.Data.SqlClient;

namespace TB.DALSqlProvider
{
    /// <summary>
    /// 对*MpUserInfo*操作数据库(针对SQL Server)的实现
    /// </summary>
    public class MpUserInfoSqlDALProvider:IDAL.IDALMpUserInfo
    { 
        public int Insert(TB.Entity.MpUserInfo obj)
        {
           //1.通过database工厂得到当前数据库连接的对象  db
            Database db = DatabaseFactory.CreateDatabase();
            //2.通过db 获得一个sp构成的cmd
            var cmd = db.GetStoredProcCommand("proc_MpUserInfo_Insert") as SqlCommand;
            //3.cmd参数赋值
            cmd.Parameters.AddWithValue("@toUserName",(obj.toUserName==String.Empty||obj.toUserName==null)?DBNull.Value:(object)obj.toUserName);
            cmd.Parameters.AddWithValue("@fromUserName",(obj.fromUserName==String.Empty||obj.fromUserName==null)?DBNull.Value:(object)obj.fromUserName);
            cmd.Parameters.AddWithValue("@createTime",(obj.createTime==String.Empty||obj.createTime==null)?DBNull.Value:(object)obj.createTime);
            cmd.Parameters.AddWithValue("@subscribe",(obj.subscribe==String.Empty||obj.subscribe==null)?DBNull.Value:(object)obj.subscribe);
            cmd.Parameters.AddWithValue("@realToUserName",(obj.realToUserName==String.Empty||obj.realToUserName==null)?DBNull.Value:(object)obj.realToUserName);
            cmd.Parameters.AddWithValue("@realFromUserName",(obj.realFromUserName==String.Empty||obj.realFromUserName==null)?DBNull.Value:(object)obj.realFromUserName);
            cmd.Parameters.AddWithValue("@sex",(obj.sex==String.Empty||obj.sex==null)?DBNull.Value:(object)obj.sex);
            cmd.Parameters.AddWithValue("@age",obj.age==0?DBNull.Value:(object)obj.age);
            cmd.Parameters.AddWithValue("@location",(obj.location==String.Empty||obj.location==null)?DBNull.Value:(object)obj.location);
            cmd.Parameters.AddWithValue("@userIcon",(obj.userIcon==String.Empty||obj.userIcon==null)?DBNull.Value:(object)obj.userIcon);
            cmd.Parameters.AddWithValue("@r1",(obj.r1==String.Empty||obj.r1==null)?DBNull.Value:(object)obj.r1);
            cmd.Parameters.AddWithValue("@r2",(obj.r2==String.Empty||obj.r2==null)?DBNull.Value:(object)obj.r2);
            cmd.Parameters.AddWithValue("@r3",(obj.r3==String.Empty||obj.r3==null)?DBNull.Value:(object)obj.r3);
            //4.重要:通过db来执行这个cmd (在db内部对cmd进行构造)
           return db.ExecuteNonQuery(cmd);
        }
        
        public int Update(TB.Entity.MpUserInfo obj)
        {
            //1.通过database工厂得到当前数据库连接的对象  db
            Database db = DatabaseFactory.CreateDatabase();
            //2.通过db 获得一个sp构成的cmd
            var cmd = db.GetStoredProcCommand("proc_MpUserInfo_Update") as SqlCommand;
            //3.cmd参数赋值
            cmd.Parameters.AddWithValue("@toUserName",(obj.toUserName==String.Empty||obj.toUserName==null)?DBNull.Value:(object)obj.toUserName);
            cmd.Parameters.AddWithValue("@fromUserName",(obj.fromUserName==String.Empty||obj.fromUserName==null)?DBNull.Value:(object)obj.fromUserName);
            cmd.Parameters.AddWithValue("@createTime",(obj.createTime==String.Empty||obj.createTime==null)?DBNull.Value:(object)obj.createTime);
            cmd.Parameters.AddWithValue("@subscribe",(obj.subscribe==String.Empty||obj.subscribe==null)?DBNull.Value:(object)obj.subscribe);
            cmd.Parameters.AddWithValue("@realToUserName",(obj.realToUserName==String.Empty||obj.realToUserName==null)?DBNull.Value:(object)obj.realToUserName);
            cmd.Parameters.AddWithValue("@realFromUserName",(obj.realFromUserName==String.Empty||obj.realFromUserName==null)?DBNull.Value:(object)obj.realFromUserName);
            cmd.Parameters.AddWithValue("@sex",(obj.sex==String.Empty||obj.sex==null)?DBNull.Value:(object)obj.sex);
            cmd.Parameters.AddWithValue("@age",obj.age==0?DBNull.Value:(object)obj.age);
            cmd.Parameters.AddWithValue("@location",(obj.location==String.Empty||obj.location==null)?DBNull.Value:(object)obj.location);
            cmd.Parameters.AddWithValue("@userIcon",(obj.userIcon==String.Empty||obj.userIcon==null)?DBNull.Value:(object)obj.userIcon);
            cmd.Parameters.AddWithValue("@r1",(obj.r1==String.Empty||obj.r1==null)?DBNull.Value:(object)obj.r1);
            cmd.Parameters.AddWithValue("@r2",(obj.r2==String.Empty||obj.r2==null)?DBNull.Value:(object)obj.r2);
            cmd.Parameters.AddWithValue("@r3",(obj.r3==String.Empty||obj.r3==null)?DBNull.Value:(object)obj.r3);
            //4.重要:通过db来执行这个cmd (在db内部对cmd进行构造)
            return db.ExecuteNonQuery(cmd);
        }
        
        public int Delete(TB.Entity.MpUserInfo obj)
        {
            //1.通过database工厂得到当前数据库连接的对象  db
            Database db = DatabaseFactory.CreateDatabase();
            //2.通过db 获得一个sp构成的cmd
            var cmd = db.GetStoredProcCommand("proc_MpUserInfo_Delete") as SqlCommand;
            //3.cmd参数赋值
           cmd.Parameters.AddWithValue("@toUserName", obj.toUserName);
           cmd.Parameters.AddWithValue("@fromUserName", obj.fromUserName);
            //4.重要:通过db来执行这个cmd (在db内部对cmd进行构造)
            return db.ExecuteNonQuery(cmd);
        }
        
        public List<TB.Entity.MpUserInfo> LoadAll()
        {
            //1.通过database工厂得到当前数据库连接的对象  db
            Database db = DatabaseFactory.CreateDatabase();
            //2.通过db 获得一个sp构成的cmd
            var cmd = db.GetStoredProcCommand("proc_MpUserInfo_LoadAll") as SqlCommand;
            //3.通过db执行一个DBReader
            var reader =  db.ExecuteReader(cmd);
            //4.遍历DBReader,生成新的Entity对象,然后添加到List
            List<TB.Entity.MpUserInfo> list = new List<TB.Entity.MpUserInfo>();
            while (reader.Read())
            {
               list.Add(new TB.Entity.MpUserInfo()
               {
                    toUserName=reader["toUserName"]==DBNull.Value?String.Empty:(string)reader["toUserName"],
                    fromUserName=reader["fromUserName"]==DBNull.Value?String.Empty:(string)reader["fromUserName"],
                    createTime=reader["createTime"]==DBNull.Value?String.Empty:reader["createTime"].ToString(),
                    subscribe=reader["subscribe"]==DBNull.Value?String.Empty:(string)reader["subscribe"],
                    realToUserName=reader["realToUserName"]==DBNull.Value?String.Empty:(string)reader["realToUserName"],
                    realFromUserName=reader["realFromUserName"]==DBNull.Value?String.Empty:(string)reader["realFromUserName"],
                    sex=reader["sex"]==DBNull.Value?String.Empty:(string)reader["sex"],
                    age=reader["age"]==DBNull.Value?0:(int)reader["age"],
                    location=reader["location"]==DBNull.Value?String.Empty:(string)reader["location"],
                    userIcon=reader["userIcon"]==DBNull.Value?String.Empty:(string)reader["userIcon"],
                    r1=reader["r1"]==DBNull.Value?String.Empty:(string)reader["r1"],
                    r2=reader["r2"]==DBNull.Value?String.Empty:(string)reader["r2"],
                    r3=reader["r3"]==DBNull.Value?String.Empty:(string)reader["r3"],
               });
           }
           return list;
        }
        
        

        public TB.Entity.MpUserInfo LoadById(string toUserName,string fromUserName)
        {
            //1.通过database工厂得到当前数据库连接的对象  db
            Database db = DatabaseFactory.CreateDatabase();
            //2.通过db 获得一个sp构成的cmd
            var cmd = db.GetStoredProcCommand("proc_MpUserInfo_SelectByIndex") as SqlCommand;
            //3.通过db执行一个DBReader
            cmd.Parameters.AddWithValue("@toUserName", toUserName);
            cmd.Parameters.AddWithValue("@fromUserName", fromUserName);
            var reader = db.ExecuteReader(cmd);
            //4.遍历DBReader,生成一个新的Entity对象 
            TB.Entity.MpUserInfo entity = null;
            while (reader.Read())
            {
                entity = new TB.Entity.MpUserInfo()
                {
                    toUserName=reader["toUserName"]==DBNull.Value?String.Empty:(string)reader["toUserName"],
                    fromUserName=reader["fromUserName"]==DBNull.Value?String.Empty:(string)reader["fromUserName"],
                    createTime=reader["createTime"]==DBNull.Value?String.Empty:reader["createTime"].ToString(),
                    subscribe=reader["subscribe"]==DBNull.Value?String.Empty:(string)reader["subscribe"],
                    realToUserName=reader["realToUserName"]==DBNull.Value?String.Empty:(string)reader["realToUserName"],
                    realFromUserName=reader["realFromUserName"]==DBNull.Value?String.Empty:(string)reader["realFromUserName"],
                    sex=reader["sex"]==DBNull.Value?String.Empty:(string)reader["sex"],
                    age=reader["age"]==DBNull.Value?0:(int)reader["age"],
                    location=reader["location"]==DBNull.Value?String.Empty:(string)reader["location"],
                    userIcon=reader["userIcon"]==DBNull.Value?String.Empty:(string)reader["userIcon"],
                    r1=reader["r1"]==DBNull.Value?String.Empty:(string)reader["r1"],
                    r2=reader["r2"]==DBNull.Value?String.Empty:(string)reader["r2"],
                    r3=reader["r3"]==DBNull.Value?String.Empty:(string)reader["r3"],
                };
            }
            return entity;
        }

        public System.Data.DataSet GetDataSet(string strSql)
        {
            Database db = DatabaseFactory.CreateDatabase();
            var cmd = db.GetSqlStringCommand(strSql) as SqlCommand;
            System.Data.DataSet ds = db.ExecuteDataSet(cmd);
            return ds;
        }


        public int UpdateUser(Entity.MpUserInfo obj)
        {
            Database db = DatabaseFactory.CreateDatabase();
            //2.通过db 获得一个sp构成的cmd
            var cmd = db.GetStoredProcCommand("proc_MpUserInfo_UpdateUser") as SqlCommand;
            //3.cmd参数赋值
            cmd.Parameters.AddWithValue("@toUserName", (obj.toUserName == String.Empty || obj.toUserName == null) ? DBNull.Value : (object)obj.toUserName);
            cmd.Parameters.AddWithValue("@fromUserName", (obj.fromUserName == String.Empty || obj.fromUserName == null) ? DBNull.Value : (object)obj.fromUserName);
            cmd.Parameters.AddWithValue("@createTime", (obj.createTime == String.Empty || obj.createTime == null) ? DBNull.Value : (object)obj.createTime);
            cmd.Parameters.AddWithValue("@subscribe", (obj.subscribe == String.Empty || obj.subscribe == null) ? DBNull.Value : (object)obj.subscribe);
            cmd.Parameters.AddWithValue("@realToUserName", (obj.realToUserName == String.Empty || obj.realToUserName == null) ? DBNull.Value : (object)obj.realToUserName);
            cmd.Parameters.AddWithValue("@realFromUserName", (obj.realFromUserName == String.Empty || obj.realFromUserName == null) ? DBNull.Value : (object)obj.realFromUserName);
            cmd.Parameters.AddWithValue("@sex", (obj.sex == String.Empty || obj.sex == null) ? DBNull.Value : (object)obj.sex);
            cmd.Parameters.AddWithValue("@age", obj.age == 0 ? DBNull.Value : (object)obj.age);
            cmd.Parameters.AddWithValue("@location", (obj.location == String.Empty || obj.location == null) ? DBNull.Value : (object)obj.location);
            cmd.Parameters.AddWithValue("@userIcon", (obj.userIcon == String.Empty || obj.userIcon == null) ? DBNull.Value : (object)obj.userIcon);
            cmd.Parameters.AddWithValue("@r1", (obj.r1 == String.Empty || obj.r1 == null) ? DBNull.Value : (object)obj.r1);
            cmd.Parameters.AddWithValue("@r2", (obj.r2 == String.Empty || obj.r2 == null) ? DBNull.Value : (object)obj.r2);
            cmd.Parameters.AddWithValue("@r3", (obj.r3 == String.Empty || obj.r3 == null) ? DBNull.Value : (object)obj.r3);
            //4.重要:通过db来执行这个cmd (在db内部对cmd进行构造)
            return db.ExecuteNonQuery(cmd);
        }
    }
}